package com.hongdee.crm.car.service.impl;

import com.gexin.rp.sdk.base.IPushResult;
import com.gexin.rp.sdk.base.impl.SingleMessage;
import com.gexin.rp.sdk.base.impl.Target;
import com.gexin.rp.sdk.exceptions.RequestException;
import com.gexin.rp.sdk.http.IGtPush;
import com.gexin.rp.sdk.template.LinkTemplate;
import com.gexin.rp.sdk.template.NotificationTemplate;
import com.gexin.rp.sdk.template.style.Style0;
import com.hongdee.common.dao.IHibernateDao;
import com.hongdee.common.dao.IImpHibernateDao;
import com.hongdee.common.dao.IImpJdbcDao;
import com.hongdee.common.dao.IJdbcDao;
import com.hongdee.common.util.DateUtils;
import com.hongdee.common.util.StringUtil;
import com.hongdee.common.util.TokenUtil;
import com.hongdee.crm.Constant;
import com.hongdee.crm.car.dao.ITCrmCarDao;
import com.hongdee.crm.car.entity.TCrmCar;
import com.hongdee.crm.car.service.ITCrmCarService;
import com.hongdee.crm.carConfig.service.ITcrmCarConfService;
import com.hongdee.crm.task.service.ICarBaseCountService;
import com.hongdee.crm.task.service.ITCrmCarWorkorderTaskService;
import com.hongdee.crm.util.IdcardInfoExtractor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by My on 2017/2/22.
 */
@Service
public class TCrmCarServiceImpl implements ITCrmCarService {
    private String tabName = "T_CRM_CAR";
    @Autowired
    private IImpHibernateDao impHibernateDao;
    @Autowired
    private IHibernateDao hibernateDao;

    @Autowired
    private IImpJdbcDao impJdbcDao;

    @Autowired
    private IJdbcDao iJdbcDao;
    @Autowired
    private ITCrmCarDao itCrmCarDao;

    @Autowired
    private ITCrmCarWorkorderTaskService itCrmCarWorkorderTaskService;
    @Autowired
    private ITcrmCarConfService confService;
    @Autowired
    private ICarBaseCountService iCarBaseCountService;

    @Override
    public Map impPage(String start, String limit, String oid) throws Exception {
        StringBuffer sqlBuffer = new StringBuffer();
        sqlBuffer.append("select * from " + tabName + " where 1=1 ");
        List<String> paramList = new ArrayList<String>();
        if (!StringUtils.isEmpty(oid)) {
            sqlBuffer.append(" and company_id = ? ");
            paramList.add(oid);
        }
        return this.impJdbcDao.queryForPageMap(start, limit, sqlBuffer.toString(), paramList.toArray());

    }

    /**
     * 获取车主车辆表
     *
     * @param PlateNamber  车牌号
     * @param maintainer   送修人 manitain_sender
     * @param maintainDate 维修日期 last_maintain_date
     * @return
     * @throws Exception
     */
    @Override
    public Map getCarAndOwner(String start, String limit, String PlateNamber, String maintainer, String maintainDate
            , String companyId, String dateStart, String dateStop, String uid) throws Exception {
        //String companyId = TokenUtil.getCompanyId(uid);
        StringBuffer sql = new StringBuffer();
        List<String> paramList = new ArrayList<String>();
        sql.append("select * from " + tabName + " where 1=1 ");
        if (!PlateNamber.isEmpty()) {
            sql.append("and plate_num like ? ");
            paramList.add("%" + PlateNamber + "%");
        }
        if (!maintainer.isEmpty()) {
            sql.append("and manitain_sender like ? ");
            paramList.add("%" + maintainer + "%");
        }
        if (!maintainDate.isEmpty()) {
            sql.append("and FROM_UNIXTIME( last_maintain_date / 1000, '%Y-%m-%d') =? ");
            paramList.add(maintainDate);
        }
        if (!companyId.isEmpty()) {
            sql.append("and company_id=? ");
            paramList.add(companyId);
        } else {
            sql.append("and company_id=? ");
            paramList.add(TokenUtil.getCompanyId(uid));
        }
        if (!dateStart.isEmpty()) {   //起始时间
            //Long dateStartL = DateUtils.dayToLongTime(dateStart);
            sql.append("and FROM_UNIXTIME(create_time/1000,'%Y-%m-%d') >=? ");
            paramList.add(dateStart);
        }
        if (!dateStop.isEmpty()) {    //结束时间
            //Long dateStopL = DateUtils.dayToLongTime(dateStop);
            sql.append("and FROM_UNIXTIME(create_time/1000,'%Y-%m-%d') <=? ");
            paramList.add(dateStop);
        }

        return this.iJdbcDao.queryForPageMap(start, limit, sql.toString(), paramList.toArray());
    }

    /**
     * 根据 实体 ID，删除记录
     *
     * @param objId
     * @return
     */
    @Override
    public boolean clearById(String objId) throws Exception {
        String hql = "delete from com.hongdee.crm.car.entity.TCrmCar where id = :id";
        Map params = new HashMap();
        params.put("id", objId);
        this.impHibernateDao.executeHqlWithParams(hql, params);
        return true;
    }

    /**
     * @param id 核销 id
     * @return Map
     * @throws Exception
     * @desc 导入确认
     * @author 徐钦勇
     */
    @Override
    public void impConfirm(String id, String uid) throws Exception {
        String sql1 = "select * from " + tabName + " where vin = ? and company_id = ? limit 1";
        Map existData;
        TCrmCar crmCar;
        crmCar = (TCrmCar) this.impHibernateDao.findEntity(TCrmCar.class, id);
        existData = this.iJdbcDao.queryForMapBySqlWithParams(sql1, crmCar.getVin(), crmCar.getCompanyId());
        //判断数据是否已存在
        if (existData.size() == 0) {
            //新数据
            crmCar.setId(null);
            crmCar.setCreateTime(System.currentTimeMillis());
            crmCar.setUpdateTime(System.currentTimeMillis());
            crmCar.setConfirmer(uid);
            crmCar.setConfirmDate(System.currentTimeMillis());
        } else {
            //已有数据
            crmCar.setId(existData.get("id").toString());
            crmCar.setUpdateTime(System.currentTimeMillis());
            crmCar.setConfirmer(uid);
            crmCar.setConfirmDate(System.currentTimeMillis());
        }
        this.hibernateDao.saveOrUpdateEntity(crmCar);
        //保存或更新
        this.clearById(id);

    }


    /**
     * @param data 核销数据的实体
     * @return Map
     * @throws Exception
     * @desc 导入确认
     * @author 术洪超
     */
    @Override
    public void impConfirms(List<Map<Object, String>> data, String uid) throws Exception {
        String sql1 = "select * from " + tabName + " where vin = ? and company_id = ? limit 1";
        List<Object> idList = new ArrayList();
        String tempId;
        Map existData;
        TCrmCar crmCar;
        for (Map car : data) {
            //查询源数据实体
            tempId = car.get("id").toString();
            crmCar = (TCrmCar) this.impHibernateDao.findEntity(TCrmCar.class, tempId);
            existData = this.iJdbcDao.queryForMapBySqlWithParams(sql1, crmCar.getVin(), crmCar.getCompanyId());
            //源数据主键
            idList.add(tempId);
            //判断数据是否已存在
            if (existData.size() == 0) {
                //新数据
                crmCar.setId(null);
                crmCar.setCreateTime(new Date().getTime());
                crmCar.setUpdateTime(new Date().getTime());
                crmCar.setConfirmer(uid);
                crmCar.setConfirmDate(new Date().getTime());
            } else {
                //已有数据
                crmCar.setId(existData.get("id").toString());
                crmCar.setUpdateTime(new Date().getTime());
                crmCar.setConfirmer(uid);
                crmCar.setConfirmDate(new Date().getTime());
            }
            //保存或更新
            this.hibernateDao.saveOrUpdateEntity(crmCar);
        }
        //满1000时，删除数据
        this.impJdbcDao.batchDel(tabName, "id", idList);
        idList.clear();
        //调用计算车辆id
        this.itCrmCarWorkorderTaskService.updWorkOrderCaridInfoQuartz();
    }


    /**
     * @return
     * @throws Exception
     * @desc 导入确认全部
     * @author 术洪超
     */
    @Override
    public void impConfirmAll(String uid) throws Exception {
        //获取公司 ID
        String companyId = TokenUtil.getCompanyId(uid);
        //目标数据库
        String targetSchema = hibernateDao.Schema();
        //来源数据库
        String sourceSchema = impHibernateDao.Schema();

        if (StringUtils.isEmpty(targetSchema) || StringUtils.isEmpty(sourceSchema)) {
            throw new Exception("未获取到数据库实例请联系管理员");
        }
        //更新SQL
        StringBuffer sb = new StringBuffer();
        sb.append(" update " + targetSchema + ".t_crm_car c1,   ");
        sb.append("        " + sourceSchema + ".t_crm_car c2  ");
        sb.append("    SET c1.active_date= c2.active_date,  ");
        sb.append("        c1.active_type= c2.active_type,  ");
        sb.append("        c1.owner_address= c2.owner_address,  ");
        sb.append("        c1.owner_birthday= c2.owner_birthday,  ");
        sb.append("        c1.brand= c2.brand,  ");
        sb.append("        c1.buy_date= c2.buy_date,  ");
        sb.append("        c1.car_age= c2.car_age,  ");
        sb.append("        c1.car_remark= c2.car_remark,  ");
        sb.append("        c1.car_type= c2.car_type,  ");
        sb.append("        c1.owner_city= c2.owner_city,  ");
        sb.append("        c1.color= c2.color,  ");
        sb.append("        c1.concat_gender= c2.concat_gender,  ");
        sb.append("        c1.configure= c2.configure,  ");
        sb.append("        c1.confirm_date= c2.confirm_date,  ");
        sb.append("        c1.confirmer= c2.confirmer,  ");
        sb.append("        c1.contact_address= c2.contact_address,  ");
        sb.append("        c1.contact_city= c2.contact_city,  ");
        sb.append("        c1.contact_name= c2.contact_name,  ");
        sb.append("        c1.contact_phone= c2.contact_phone,  ");
        sb.append("        c1.contact_postcode= c2.contact_postcode,  ");
        sb.append("        c1.contact_province= c2.contact_province,  ");
        sb.append("        c1.contact_tel= c2.contact_tel,  ");
        sb.append("        c1.corp_code= c2.corp_code,  ");
        sb.append("        c1.corp_type= c2.corp_type,  ");
        sb.append("        c1.doc_date= c2.doc_date,  ");
        sb.append("        c1.doc_user= c2.doc_user,  ");
        sb.append("        c1.owner_education= c2.owner_education,  ");
        sb.append("        c1.owner_email= c2.owner_email,  ");
        sb.append("        c1.engine_num= c2.engine_num,  ");
        sb.append("        c1.first_enter_date= c2.first_enter_date,  ");
        sb.append("        c1.first_maintain_mile= c2.first_maintain_mile,  ");
        sb.append("        c1.guarantee_end_date= c2.guarantee_end_date,  ");
        sb.append("        c1.guarantee_start_date= c2.guarantee_start_date,  ");
        sb.append("        c1.is_base= c2.is_base,  ");
        sb.append("        c1.last_maintain_date= c2.last_maintain_date,  ");
        sb.append("        c1.licence_date= c2.licence_date,  ");
        sb.append("        c1.lost_month= c2.lost_month,  ");
        sb.append("        c1.manitain_sender= c2.manitain_sender,  ");
        sb.append("        c1.maintain_start_mile= c2.maintain_start_mile,  ");
        sb.append("        c1.owner_marry= c2.owner_marry,  ");
        sb.append("        c1.model= c2.model,  ");
        sb.append("        c1.model_year= c2.model_year,  ");
        sb.append("        c1.owner_month_income= c2.owner_month_income,  ");
        sb.append("        c1.need_fee= c2.need_fee,  ");
        sb.append("        c1.owner_code= c2.owner_code,  ");
        sb.append("        c1.owner_gender= c2.owner_gender,  ");
        sb.append("        c1.owner_identity= c2.owner_identity,  ");
        sb.append("        c1.owner_level= c2.owner_level,  ");
        sb.append("        c1.owner_name= c2.owner_name,  ");
        sb.append("        c1.owner_phone= c2.owner_phone,  ");
        sb.append("        c1.owner_property= c2.owner_property,  ");
        sb.append("        c1.owner_remark= c2.owner_remark,  ");
        sb.append("        c1.owner_score= c2.owner_score,  ");
        sb.append("        c1.owner_tel= c2.owner_tel,  ");
        sb.append("        c1.owner_type= c2.owner_type,  ");
        sb.append("        c1.plate_num= c2.plate_num,  ");
        sb.append("        c1.owner_post_code= c2.owner_post_code,  ");
        sb.append("        c1.pre_fee= c2.pre_fee,  ");
        sb.append("        c1.prev_maintain_date= c2.prev_maintain_date,  ");
        sb.append("        c1.owner_province= c2.owner_province,  ");
        sb.append("        c1.remindstatus= c2.remindstatus,  ");
        sb.append("        c1.run_mile= c2.run_mile,  ");
        sb.append("        c1.sa_id= c2.sa_id,  ");
        sb.append("        c1.saler= c2.saler,  ");
        sb.append("        c1.sender_gender= c2.sender_gender,  ");
        sb.append("        c1.sender_phone= c2.sender_phone,  ");
        sb.append("        c1.sender_phone_qh= c2.sender_phone_qh,  ");
        sb.append("        c1.sender_tel= c2.sender_tel,  ");
        sb.append("        c1.series= c2.series,  "); //c1.store_name = c2.store_name,
        sb.append("        c1.store_name = c2.store_name,  ");
        sb.append("        c1.status= c2.status,  ");
        sb.append("        c1.tags= c2.tags,  ");
        sb.append("        c1.owner_trader= c2.owner_trader,  ");
        sb.append("        c1.vin= c2.vin,  ");
        sb.append("        c1.yearcheck_date= c2.yearcheck_date,  " +
                "          c1.wx_id= c2.wx_id  ");
        sb.append(" where  c1.plate_num = c2.plate_num  ");
        sb.append("    and c1.company_id= c2.company_id   and c1.company_id = '" + companyId + "' ");
        //删除已存在记录
        StringBuffer delExistSQL = new StringBuffer();
        String targeTable = targetSchema + ".t_crm_car";
        String sourceTable = sourceSchema + ".t_crm_car";

        delExistSQL.append("delete from " + sourceTable + " USING " + targeTable + "," + sourceTable + " WHERE ");
        delExistSQL.append(" " + targeTable + ".company_id = " + sourceTable + ".company_id  and " + targeTable + ".plate_num  = " + sourceTable + ".plate_num  and " + targeTable + ".company_id ='" + companyId + "'");

        // delExistSQL.append(" DELETE FROM " + sourceSchema + ".t_crm_car where EXISTS(select 1 from " + targetSchema + ".t_crm_car c2 where c2.company_id= company_id and c2.vin= vin  ) and company_id = '" + companyId + "' ");
        // 新增SQL
        StringBuffer selectField = new StringBuffer();
        selectField.append("   id,");
        selectField.append("   create_time,");
        selectField.append("   create_user,");
        selectField.append("   update_time,");
        selectField.append("   update_user,");
        selectField.append("   active_date,");
        selectField.append("   active_type,");
        selectField.append("   owner_address,");
        selectField.append("   owner_birthday,");
        selectField.append("   brand,");
        selectField.append("   buy_date,");
        selectField.append("   car_age,");
        selectField.append("   car_remark,");
        selectField.append("   car_type,");
        selectField.append("   owner_city,");
        selectField.append("   color,");
        selectField.append("   company_id,");
        selectField.append("   concat_gender,");
        selectField.append("   configure,");
        selectField.append("   confirm_date,");
        selectField.append("   confirmer,");
        selectField.append("   contact_address,");
        selectField.append("   contact_city,");
        selectField.append("   contact_name,");
        selectField.append("   contact_phone,");
        selectField.append("   contact_postcode,");
        selectField.append("   contact_province,");
        selectField.append("   contact_tel,");
        selectField.append("   corp_code,");
        selectField.append("   corp_type,");
        selectField.append("   doc_date,");
        selectField.append("   doc_user,");
        selectField.append("   owner_education,");
        selectField.append("   owner_email,");
        selectField.append("   engine_num,");
        selectField.append("   first_enter_date,");
        selectField.append("   first_maintain_mile,");
        selectField.append("   from_company,");
        selectField.append("   guarantee_end_date,");
        selectField.append("   guarantee_start_date,");
        selectField.append("   insurance_code,");
        selectField.append("   insurance_end_date,");
        selectField.append("   insurance_fee,");
        selectField.append("   insurance_name,");
        selectField.append("   insurance_ordernum,");
        selectField.append("   insurance_start_date,");
        selectField.append("   is_base,");
        selectField.append("   last_maintain_date,");
        selectField.append("   licence_date,");
        selectField.append("   lost_month,");
        selectField.append("   manitain_sender,");
        selectField.append("   maintain_start_mile,");
        selectField.append("   owner_marry,");
        selectField.append("   model,");
        selectField.append("   model_year,");
        selectField.append("   owner_month_income,");
        selectField.append("   need_fee,");
        selectField.append("   next_maintain_date,");
        selectField.append("   next_maintain_mile,");
        selectField.append("   owner_code,");
        selectField.append("   owner_gender,");
        selectField.append("   owner_identity,");
        selectField.append("   owner_level,");
        selectField.append("   owner_name,");
        selectField.append("   owner_phone,");
        selectField.append("   owner_property,");
        selectField.append("   owner_remark,");
        selectField.append("   owner_score,");
        selectField.append("   owner_tel,");
        selectField.append("   owner_type,");
        selectField.append("   plate_num,");
        selectField.append("   owner_post_code,");
        selectField.append("   pre_fee,");
        selectField.append("   prev_maintain_date,");
        selectField.append("   owner_province,");
        selectField.append("   remindstatus,");
        selectField.append("   run_mile,");
        selectField.append("   sa_id,");
        selectField.append("   saler,");
        selectField.append("   sender_gender,");
        selectField.append("   sender_phone,");
        selectField.append("   sender_phone_qh,");
        selectField.append("   sender_tel,");
        selectField.append("   series,");
        selectField.append("   status,");
        selectField.append("   tags,");
        selectField.append("   owner_trader,");
        selectField.append("   vin,");
        selectField.append("   yearcheck_date,");
        selectField.append("   wx_id,");
        selectField.append("   mile_status,");
        selectField.append("   store_name");
        StringBuffer insertSQL = new StringBuffer();
        insertSQL.append(" insert into " + targetSchema + ".t_crm_car(").append(selectField.toString()).append(") ").append(" select ");
        insertSQL.append(selectField.toString()).append(" from " + sourceSchema + ".t_crm_car where  company_id = '" + companyId + "'");

        // insertSQL.append(" INSERT INTO " + targetSchema + ".t_crm_car select * from " + sourceSchema + ".t_crm_car c1 where c1.company_id = '" + companyId + "' ");
        //更新记录数
        int upRowCount = iJdbcDao.update(sb.toString());
        System.out.println("-------------------更新车辆数---------" + upRowCount);
        //删除更新记录数
        int delRowCount = iJdbcDao.update(delExistSQL.toString());
        System.out.println("-------------------删除更新车辆数---------" + delRowCount);

        //新增记录数
        int addRowCount = iJdbcDao.update(insertSQL.toString());
        System.out.println("-------------------新增车辆数---------" + addRowCount);

        //删除新增记录数
        int delAddRowCount = iJdbcDao.update(delExistSQL.toString());

        System.out.println("-------------------删除新增车辆数---------" + delAddRowCount);


//        //编写查询源数据 HQL
//        String hql = "from TCrmCar where companyId = :companyId";
//        //设置参数
//        Map<String, Object> params = new HashMap();
//        params.put("companyId", companyId);
//        //查询公司下的全部车辆
//        List<TCrmCar> tCrmCar = impHibernateDao.findEntityListByHqlWithParams(hql, params);
//        if (tCrmCar == null) {
//            throw new Exception("无效的数据！");
//        }
//        //根据vin判断目标数据是否已存在
//        String sql1 = "select * from " + tabName + " where vin = ? and company_id = ? limit 1";
//        List<Object> idList = new ArrayList();
//        String tempId;
//        Map existData;
//        for (TCrmCar car : tCrmCar) {
//            existData = this.iJdbcDao.queryForMapBySqlWithParams(sql1, car.getVin(), car.getCompanyId());
//            //源数据主键
//            tempId = car.getId();
//            idList.add(tempId);
//            //判断数据是否已存在
//            if (existData.size() == 0) {
//                //新数据
//                car.setId(null);
//                car.setCreateTime(new Date().getTime());
//                car.setUpdateTime(new Date().getTime());
//                car.setConfirmer(uid);
//                car.setConfirmDate(new Date().getTime());
//            } else {
//                //已有数据
//                car.setId(existData.get("id").toString());
//                car.setUpdateTime(new Date().getTime());
//                car.setConfirmer(uid);
//                car.setConfirmDate(new Date().getTime());
//            }
//            //保存或更新
//            this.hibernateDao.saveOrUpdateEntity(car);
//            //满1000时，删除数据
//            if (idList.size() == 1000) {
//                this.impJdbcDao.batchDel(tabName, "id", idList);
//                idList.clear();
//            }
//        }
//        //删除遗留数据
//        if (idList.size() > 0) {
//            this.impJdbcDao.batchDel(tabName, "id", idList);
//            idList.clear();
//        }
        //调用计算车辆id
        this.itCrmCarWorkorderTaskService.updWorkOrderCaridInfoQuartz();
        //计算车辆车辆
        this.iCarBaseCountService.countCarAgeQuartz();
        //车型车系同步
        this.iCarBaseCountService.updateExCarSeriesQuartz();
        //更新车牌号信息
        //结尾以及警、学的将学、警移至车牌号前面
        //车牌号为无牌照或其它字段的 将VIN设置为牌照
        this.updCrmCarPlateNum(companyId);
    }


    private void updCrmCarPlateNum(String companyId) throws Exception {
        String[] type = {"学", "警"};
        for (String str : type) {
            String sql = "update t_crm_car set plate_num= CONCAT(RIGHT(plate_num,1),LEFT(plate_num,char_LENGTH(plate_num)-1))" +
                    " where RIGHT(plate_num,1)=? and company_id=? ";
            this.itCrmCarDao.executeSQL(sql, str, companyId);

        }
        String[] type2 = {"无牌照", ""};
        for (String str : type2) {
            if (str.equals("")) {
                String sql = "update t_crm_car set plate_num=right(vin,6)  where plate_num is null and company_id=?";
                this.itCrmCarDao.executeSQL(sql, companyId);
            } else {
                String sql = "update t_crm_car set plate_num=right(vin,6)  where plate_num=? and company_id=?";
                this.itCrmCarDao.executeSQL(sql, str, companyId);
            }
        }

    }

    @Override
    public void impDels(List<Map<Object, String>> data, String uid) throws Exception {

        List<Object> idList = new ArrayList();
        for (Map map : data) {
            idList.add(map.get("id"));
        }
        this.impJdbcDao.batchDel(tabName, "id", idList);
    }


    /**
     * @return
     * @throws Exception
     * @desc 删除全部待导入数据
     * @author 术洪超
     */
    @Override
    public void impDelAll(String uid) throws Exception {

        Map map = new HashMap();
        String companyId = TokenUtil.getCompanyId(uid);
        String hql = " delete from TCrmCar where companyId =:companyId";
        map.put("companyId", companyId);
        //System.out.println(companyId);

        this.impHibernateDao.executeHqlWithParams(hql, map);
    }


    /**
     * @param
     * @return Map
     * @throws Exception
     * @desc 单客户维系
     * @author 徐钦勇
     */
    @Override
    public Map page(String start, String limit, String uid, String companyId ,String id,String key,String value) throws Exception {
        if (StringUtils.isEmpty(companyId)) {
            companyId = TokenUtil.getCompanyId(uid);
        }

        // String oid = TokenUtil.getCompanyId(uid);

        StringBuffer sqlBuffer = new StringBuffer();
        StringBuffer sqlSH = new StringBuffer();
        StringBuffer sqlSX = new StringBuffer();
        StringBuffer sqlYB = new StringBuffer();
        StringBuffer sqlXB = new StringBuffer();
        StringBuffer sqlTask = new StringBuffer();
        StringBuffer sqlCarLabel = new StringBuffer();
        StringBuffer sqlCustomerLabel = new StringBuffer();
        List exTable = new ArrayList();
        List inTable = new ArrayList();
        List taskTable = new ArrayList();
        List CustomerTabel = new ArrayList();
        List params = new ArrayList();
        List list = new ArrayList();
        Map Total = new HashMap();
        sqlBuffer.append("select                                             ");
        sqlBuffer.append("tc.id,                                             ");
        sqlBuffer.append("tc.plate_num,                                      ");
        sqlBuffer.append("tc.brand,                                          ");
        sqlBuffer.append("tc.wx_id,                                          "); // 新增维系人
        sqlBuffer.append("tc.cx_tag,                                          "); // 新增促销类型标签
        sqlBuffer.append("tc.model,                                          ");
        sqlBuffer.append("tc.series,                                         ");
        sqlBuffer.append("tc.configure,                                      ");
        sqlBuffer.append("tc.model_year,                                     ");
        sqlBuffer.append("tc.color,                                          ");
        sqlBuffer.append("tc.vin,                                            ");
        sqlBuffer.append("tc.engine_num,                                     ");
        sqlBuffer.append("tc.buy_date,                                       ");
        sqlBuffer.append("tc.tags,                                  ");
        sqlBuffer.append("tc.licence_date,                                   ");
        sqlBuffer.append("tc.guarantee_start_date,                           ");
        sqlBuffer.append("tc.first_enter_date,                               ");
        sqlBuffer.append("tc.maintain_start_mile,                            ");
        sqlBuffer.append("tc.run_mile ,                                      ");
        sqlBuffer.append("tc.next_maintain_mile,                             ");
        sqlBuffer.append("tc.prev_maintain_date,                             ");
        sqlBuffer.append("tc.next_maintain_date,                             ");
        sqlBuffer.append("tc.owner_name,                                     ");
        sqlBuffer.append("tc.owner_gender,                                   ");
        sqlBuffer.append("tc.owner_property,                                 ");
        sqlBuffer.append("tc.owner_code,                                     ");
        sqlBuffer.append(" DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0), INTERVAL tc.owner_birthday/1000 SECOND),'%Y-%m-%d') owner_birthday, ");
        sqlBuffer.append("tc.owner_tel,                                      ");
        sqlBuffer.append("tc.owner_address,                                  ");
        sqlBuffer.append("tc.owner_trader,                                   ");
        sqlBuffer.append("tc.owner_month_income,                             ");
        sqlBuffer.append("tc.owner_marry,                                    ");
        sqlBuffer.append("tc.owner_education,                                ");
        sqlBuffer.append("tc.contact_name,                                   ");
        sqlBuffer.append("tc.concat_gender,                                  ");
        sqlBuffer.append("tc.contact_tel,                                    ");
        sqlBuffer.append("tc.contact_address,                                ");
        sqlBuffer.append("tc.manitain_sender,                                ");
        sqlBuffer.append("tc.sender_gender,                                  ");
        sqlBuffer.append("tc.sender_tel,                                     ");
        sqlBuffer.append("tc.market_tag,                                     ");
        sqlBuffer.append("tc.owner_identity,                                 ");
        sqlBuffer.append("tc.owner_phone,tc.wx_id,tc.sa_id,sa.u_name as saName,tc.kf_id, kf.u_name as kfName,tc.bx_id,bx.u_name as bxName,tc.saler,                             ");
        sqlBuffer.append("case when tc.from_company = '是' then '本公司' else '非本公司' end isCompany ");
        sqlBuffer.append("FROM t_crm_car tc " +
                " left join t_bc_user sa on tc.sa_id=sa.id      " +
                " left join t_bc_user kf on tc.kf_id=kf.id   " +
                " left join t_bc_user bx on tc.bx_id= bx.id ");
        sqlBuffer.append("where tc.company_id = ? ");
        params.add(companyId);

        if(!id.equals("")){
            sqlBuffer.append("and tc.id = ? ");
            params.add(id);
        }

//        Constant.VIN_TO_SQL(sqlBuffer, uid, "tc");

        /**2017年10月19日10:24:45 修改 单客户查询权限放开**/
        /*if (companyId.equals(TokenUtil.getCompanyId(uid))) {
            confService.crmCarConfToSql(uid, sqlBuffer, "tc", companyId);
        }*/

//        // 查询其分配的车辆
//        Map info = refCar(uid);
//        //未分配车辆
//        if (null == info || info.size() == 0) {
//            throw new Exception("该用户未分配车辆，请联系管理员！");
//        }
//
//        //车辆分配类型
//        String refType = String.valueOf(info.get("type"));
//        if (StringUtils.isEmpty(refType) || "null".equals(refType.toLowerCase())) {
//            throw new Exception("该用户未分配车辆，请联系管理员！");
//        }
//        //车辆分配值，车属号 或车尾数，vin时 另行处理
//        String refValue = String.valueOf(info.get("value"));
//        refValue = refValue.replaceAll(",", "");
//        //根据类型取车辆
//
//        switch (refType) {
//            case Constant.CAR_WH:
//                sqlBuffer.append(" AND  tc.plate_num REGEXP ? ");
//                params.add("[" + refValue + "]$");
//                break;
//            case Constant.CAR_WS:
//                //先不写
//                sqlBuffer.append(" AND tc.plate_num REGEXP ? ");
//                params.add(".*[" + refValue + "][A-Za-z]*$");
//                //根据尾数查询
//                break;
//            case Constant.CAR_ALL:
//                //全部车辆
//                break;
//            default:
//                throw new Exception("车辆分配错误，请联系管理员！");
//        }

        //只能查询登陆者自己所分配的车辆
        if (!value.isEmpty() && !key.isEmpty()) {
            switch (key) {
                //车牌号
                case "0": {
                    sqlBuffer.append(" and tc.plate_num like ? ");
                    params.add("%" + value + "%");
                }
                break;
                //vin
                case "1": {
                    sqlBuffer.append(" and tc.vin like ? ");
                    params.add("%" + value + "%");
                }
                break;
                //车主姓名
                case "2": {
                    sqlBuffer.append(" and tc.owner_name like ? ");
                    params.add("%" + value + "%");
                }
                break;
                //车主手机号
                case "3": {
                    sqlBuffer.append(" and tc.owner_tel like ? ");
                    params.add("%" + value + "%");
                }
                break;
                //送修人姓名
                case "4": {
                    sqlBuffer.append(" and tc.manitain_sender like ? ");
                    params.add("%" + value + "%");
                }
                break;
                //送修人手机
                case "5": {
                    sqlBuffer.append(" and tc.sender_tel like ? ");
                    params.add("%" + value + "%");
                }
                break;
            }
        }

        //sqlBuffer.append(" LIMIT 1 ");
        //System.out.print(sqlBuffer.toString());
        Map CarInfo = this.itCrmCarDao.page(sqlBuffer.toString(), params.toArray(), "0", "1");
        ArrayList tempList = (ArrayList) CarInfo.get("dataList");
        if (tempList.size() == 0) {
            throw new Exception("未能查询有效车辆");
        }
        /**
         * 绑定车辆id
         */
        List carinfo = (ArrayList) CarInfo.get("dataList");
        Map info2 = (HashMap) carinfo.get(0);
        list.add(info2.get("id"));

        /**
         * 根据查询出的车辆id查询车辆标签
         */
        sqlCarLabel.append(" SELECT label FROM t_crm_car_property where car_id = ? ");
        List tempTabel = this.itCrmCarDao.list(sqlCarLabel.toString(), list.toArray());

        /**
         * 根据车主手机号查询卡信息 2018年3月28日15:08:28新增
         */
        List cardListParam = new ArrayList();
        String cardOwnerTel = (String) info2.get("ownerTel");
        List cardTabel = new ArrayList();
        if(cardOwnerTel != null && !cardOwnerTel.equals("")){
            String cardSql = " SELECT * FROM t_crm_card WHERE tel_num = ? ";
            cardListParam.add(cardOwnerTel);
            cardTabel = this.itCrmCarDao.list(cardSql,cardListParam.toArray());
        }



        /**
         * 根据查询出的客户标签tags联查到客户标签组
         */
        if (info2.get("tags") != null) {
            String[] temp = info2.get("tags").toString().split(",");
            List CustomerParams = new ArrayList();
            // List<Map<String,Object>> CustomerTemp = new ArrayList<Map<String,Object>>();
            sqlCustomerLabel.append(" select label from t_bc_newdict where p_code = '02030106' and code in ( ");
            for (String tag : temp) {
                sqlCustomerLabel.append(" ? ,");
                CustomerParams.add(tag);
            }
            sqlCustomerLabel.append(" '' )");
            CustomerTabel = this.itCrmCarDao.list(sqlCustomerLabel.toString(), CustomerParams.toArray());
//            for(Map map : CustomerTemp){
//                CustomerTabel.add(map.get("label"));
//            }
        }
        //String companyId=TokenUtil.getCompanyId(uid);
        /**
         * 根据查询出的车辆客户信息中车辆id查询工单明细
         */
        Map CarAfterSale = new HashMap();
        sqlSH.append(" SELECT * from T_CRM_CAR_WORKORDER where 1=1 ");
        /**
         * 修正工单倒序
         * **/
        sqlSH.append(" and vin = ? and company_id=? order by open_date desc ");
        CarAfterSale = this.itCrmCarDao.page(sqlSH.toString(), new Object[]{info2.get("vin"), companyId}, start, limit);
        /**
         * 根据查询出的车辆客户信息中车辆id查询所有工单属性值
         */
        sqlSX.append("SELECT tw.car_id,");
        //售后总产值,索赔按维修金额，维修按结算金额
        sqlSX.append("SUM(tw.real_amount) sum,");
        //一年内产值
        sqlSX.append("SUM(CASE WHEN (tw.balance_date BETWEEN ? AND ? ) THEN tw.real_amount ELSE 0 END) sumOfYear, ");
        //总进厂次数
        sqlSX.append("COUNT(DISTINCT from_unixtime(tw.balance_date/1000,'%Y-%m-%d')) totalIn,");
        //当年进厂次数
        sqlSX.append("COUNT(DISTINCT IF(tw.balance_date BETWEEN ? AND ? , from_unixtime(tw.balance_date/1000,'%Y-%m-%d'),NULL) )  totalInYear, ");
        //首次进厂时间
        sqlSX.append("tc.first_enter_date ");
        sqlSX.append("FROM t_crm_car_workorder tw ");
        sqlSX.append("LEFT JOIN t_crm_car tc ON tw.car_id=tc.id ");
        sqlSX.append("WHERE tw.vin=? and tw.company_id=? and tw.real_amount>'0.00' ");
        sqlSX.append("GROUP BY tw.car_id");
        list.clear();
        Date date = new Date();
        Long stopTime = date.getTime();
        Long startTime = DateUtils.addMonthLongTime(stopTime, -12);
        list.add(startTime);
        list.add(stopTime);
        list.add(startTime);
        list.add(stopTime);
        list.add(info2.get("vin"));
        list.add(companyId);
        Map WorkOrderProperty = this.itCrmCarDao.info(sqlSX.toString(), list.toArray());
        if (info2.get("vin") != null) {
            List listVin = new ArrayList();
            listVin.add(info2.get("vin"));
            listVin.add(companyId);
            /**
             * 根据carInfo中的VIN查询延保记录
             */
            sqlYB.append("select restart_insurance,restop_insurance,type_name,start_legend, ");
            sqlYB.append("stop_legend,mile_status from t_crm_car_exsurance where vin = ? and company_id=?");
            exTable = this.itCrmCarDao.list(sqlYB.toString(), listVin.toArray());
            /**
             * 根据carInfo中的VIN查询续保（合同）记录
             */
            sqlXB.append("select  compulsoryinsurancenum,compulsory_start_date,compulsory_end_date,commercialinsurancenum , ");
            sqlXB.append("contractstartinsurance,contractstopinsurance,base_type,continuation from t_crm_car_insurance where vin = ? and company_id=? ");
            inTable = this.itCrmCarDao.list(sqlXB.toString(), listVin.toArray());
            /**
             * 根据carInfo中的VIN查询任务记录
             */
            sqlTask.append("select * from t_crm_task_real where vin = ? AND status = '0' and company_id=? ");
            taskTable = this.itCrmCarDao.list(sqlTask.toString(), listVin.toArray());
            Total.put("taskTable", taskTable);
        }
        //车辆信息
        Total.put("CarInfo", CarInfo);
        //车辆标签
        ybLabel(exTable, tempTabel);//设置延保标签
        xbLabel(inTable, tempTabel);
        //设置任务标签
        if (taskTable.size() > 0) {
            Map temp = new HashMap();
            temp.put("label", "任务");
            tempTabel.add(temp);
        }
        Total.put("CarTabel", tempTabel);
        //客户标签
        Total.put("CustomerTabel", CustomerTabel);
        //售后工单
        Total.put("CarAfterSale", CarAfterSale);
        //工单属性
        Total.put("WorkOrderProperty", WorkOrderProperty);
        //延保记录
        Total.put("exTable", exTable);
        //续保记录（合同）
        Total.put("inTable", inTable);
        // 卡信息
        Total.put("cardTabel",cardTabel);
        //System.out.print(Total);
        return Total;
    }

    /**
     * @Name: 单车主多车辆查询
     * @Author: Liu XiaoLong
     * @Date: 2018/1/18 10:51
     * @Remark:
     */
    @Override
    public Map pageList(String start, String limit, String key, String value, String uid, String companyId) throws Exception {
        String companyIdByUid = TokenUtil.getCompanyId(uid);
        StringBuffer sql = new StringBuffer();
        List param = new ArrayList();

        sql.append("SELECT * FROM t_crm_car WHERE company_id = ? ");
        param.add(companyIdByUid);

        if (!value.isEmpty() && !key.isEmpty()) {
            switch (key) {
                //车牌号
                case "0": {
                    sql.append(" and plate_num like ? ");
                    param.add("%" + value + "%");
                }
                break;
                //vin
                case "1": {
                    sql.append(" and vin like ? ");
                    param.add("%" + value + "%");
                }
                break;
                //车主姓名
                case "2": {
                    sql.append(" and owner_name like ? ");
                    param.add("%" + value + "%");
                }
                break;
                //车主手机号
                case "3": {
                    sql.append(" and owner_tel like ? ");
                    param.add("%" + value + "%");
                }
                break;
                //送修人姓名
                case "4": {
                    sql.append(" and manitain_sender like ? ");
                    param.add("%" + value + "%");
                }
                break;
                //送修人手机
                case "5": {
                    sql.append(" and sender_tel like ? ");
                    param.add("%" + value + "%");
                }
                break;
            }
        } else {
            throw new Exception("请选择查询类型并填写查询内容！");
        }
        return itCrmCarDao.page(sql.toString(),param.toArray(),start,limit);
    }

    /**
     * 设置延保标签
     *
     * @param exTable
     * @param labels
     */
    private void ybLabel(List<Map> exTable, List labels) {
        for (Map carInfoTemp : exTable) {
            if (carInfoTemp.get("mileStatus") != null) {
                Integer mileStatus = Integer.valueOf(String.valueOf(carInfoTemp.get("mileStatus")));
                Map temp = new HashMap();
                if (mileStatus < 2) {
                    temp.put("label", "延保");
                    labels.add(temp);
                    break;
                }
            }
        }
    }

    /**
     * 设置续保标签
     *
     * @param inTable
     * @param labels
     */
    private void xbLabel(List<Map> inTable, List labels) {
        Long nowTime = new Date().getTime();
        Map temp = new HashMap();
        for (Map carInfoTemp : inTable) {
            //商业险到期时间未过期
            if (carInfoTemp.get("contractstopinsurance") != null) {
                String continuation = StringUtil.formatEmpty(carInfoTemp.get("continuation"));
                Long contractstopinsurance = Long.valueOf(String.valueOf(carInfoTemp.get("contractstopinsurance")));
                if (contractstopinsurance > nowTime) {
                    Map conMap = new HashMap();
                    if (!"".equals(continuation)) {
                        String conStr = "";
                        if ("3".equals(continuation)) {
                            conStr = "三连保";
                        }
                        if ("2".equals(continuation)) {
                            conStr = "二连保";
                        }
                        conMap.put("label", conStr);
                        labels.add(conMap);
                    }
                    temp.put("label", "续保");
                    labels.add(temp);
                    break;
                }
            }
            if (carInfoTemp.get("compulsoryEndDate") != null) {
                Long compulsoryEndDate = Long.valueOf(String.valueOf(carInfoTemp.get("compulsoryEndDate")));
                if (compulsoryEndDate > nowTime) {
                    temp.put("label", "续保");
                    labels.add(temp);
                    break;
                }
            }
        }
    }

    /**
     * 设置延保标签
     *
     * @param taskTable
     * @param labels
     */
    private void taskLabel(List<Map> taskTable, List labels) {
        for (Map taskTemp : taskTable) {
            if (taskTemp.get("mileStatus") != null) {
                Integer mileStatus = Integer.valueOf(String.valueOf(taskTemp.get("mileStatus")));
                Map temp = new HashMap();
                if (mileStatus < 2) {
                    temp.put("label", "延保");
                    labels.add(temp);
                    break;
                }
            }
        }
    }

    /**
     * @param id       车主车辆信息
     * @param plateNum 车牌号
     * @return Map
     * @throws Exception
     * @desc 变更车牌号
     * @author 徐钦勇
     */
    @Override
    public void ChangePlate(String id, String plateNum) throws Exception {
        String sql = " update t_crm_car set plate_num = ? where id = ? ";
        List list = new ArrayList();
        list.add(plateNum);
        list.add(id);
        this.itCrmCarDao.executeSQL(sql, list.toArray());
    }

    /**
     * @param id 车主车辆信息
     * @param
     * @return Map
     * @throws Exception
     * @desc 变更送修人信息
     * @author 徐钦勇
     */
    @Override

    public void ChangeSendor(String id, String name, String sex, String phone, Map map) throws Exception {
//        String sql = " update t_crm_car set manitain_sender = ? , sender_gender = ? , sender_tel = ?,sender_phone = ?,owner_name=?" +
//                ",owner_phone=?,owner_tel=?,owner_gender=?,owner_identity=?,owner_address=? ,wx_id = ? ,saler = ? where id = ? ";
//
        List list = new ArrayList();

        StringBuffer sqlBuf = new StringBuffer();
        sqlBuf.append(" update t_crm_car set car_age = car_age ");

        if (!StringUtils.isEmpty(map.get("senderName"))) {
            sqlBuf.append(" , manitain_sender = ?");
            list.add(map.get("senderName"));
        }

        /*送车人信息*/

        if (!StringUtils.isEmpty(map.get("senderSex"))) {
            sqlBuf.append(" , sender_gender = ?");
            list.add(map.get("senderSex"));
        }
        //list.add(map.get("senderPhone"));
        if (!StringUtils.isEmpty(map.get("senderPhone"))) {
            sqlBuf.append(" , sender_tel = ?");
            list.add(map.get("senderPhone"));
        }
        //list.add(map.get("senderPhone"));
        if (!StringUtils.isEmpty(map.get("senderPhone"))) {
            sqlBuf.append(" , sender_phone = ?");
            list.add(map.get("senderPhone"));
        }
        /*车主信息*/
        //list.add(map.get("name"));
        if (!StringUtils.isEmpty(map.get("name"))) {
            sqlBuf.append(" , owner_name = ?");
            list.add(map.get("name"));
        }
        // list.add(map.get("tel"));
        if (!StringUtils.isEmpty(map.get("tel"))) {
            sqlBuf.append(" , owner_tel = ?");
            list.add(map.get("tel"));
        }
        // list.add(map.get("phone"));
        if (!StringUtils.isEmpty(map.get("phone"))) {
            sqlBuf.append(" , owner_phone = ?");
            list.add(map.get("phone"));
        }

        if (!StringUtils.isEmpty(map.get("contactTel"))) {
            sqlBuf.append(" , contact_tel = ?");
            list.add(map.get("contactTel"));
        }

        //list.add(map.get("sex"));
        if (!StringUtils.isEmpty(map.get("sex"))) {
            sqlBuf.append(" , owner_gender = ?");
            list.add(map.get("sex"));
        }
        // list.add(map.get("card"));
        if (!StringUtils.isEmpty(map.get("card"))) {
            sqlBuf.append(" , owner_identity = ?");
            list.add(map.get("card"));
        }
        //list.add(map.get("addr"));
        if (!StringUtils.isEmpty(map.get("addr"))) {
            sqlBuf.append(" , owner_address = ?");
            list.add(map.get("addr"));
        }

        //list.add(map.get("wx_id"));
        if (!StringUtils.isEmpty(map.get("wx_id"))) {
            sqlBuf.append(" , wx_id = ?");
            list.add(map.get("wx_id"));
        }
        //list.add(map.get("saler"));
        if (!StringUtils.isEmpty(map.get("saler"))) {
            sqlBuf.append(" , saler = ?");
            list.add(map.get("saler"));
        }
        sqlBuf.append(" where id = ? ");
        list.add(id);
        this.itCrmCarDao.executeSQL(sqlBuf.toString(), list.toArray());
    }

    /***
     * 车辆分配
     * @param uid
     * @return
     * @throws Exception
     */
    private Map<String, Object> refCar(String uid) throws Exception {
        String sql = "select  * from t_crm_car_ref where u_id = ? and status = '1' ";
        Map info = this.itCrmCarDao.info(sql, uid);
        return info;
    }

    /**
     * 查询没有质保状态或未出保的车辆信息
     *
     * @param start
     * @param limit
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String, Object>> queryCarWarrantyList(int start, int limit) throws Exception {
        String sql = "select id,company_id,run_mile,guarantee_start_date,guarantee_end_date,brand," +
                "series from t_crm_car where mile_status is null or mile_status<2 limit ?,?";
        return itCrmCarDao.list(sql, start, limit);
    }

    /**
     * @Name: 基盘车辆区域分布
     * @Author: Liu XiaoLong
     * @Date: 2017/8/28 13:47
     */
    @Override
    public Map<String, Object> baseCar(String uid, String start, String limit, Map map) throws Exception {

        if (!StringUtils.isEmpty(map.get("companyId"))) {
            StringBuffer sql = new StringBuffer();
            List params = new ArrayList();
            /**城市查询**/
            sql.append("SELECT id companyId,o_city,o_name,a.city FROM  t_bc_organ b LEFT JOIN ( SELECT cityid,city FROM t_bc_cities) a ON o_city = a.cityid WHERE b.id = ? ");
            params.add(map.get("companyId"));
            Map companyMap = itCrmCarDao.info(sql.toString(), params.toArray());
            String companyCity = (String) companyMap.get("city"); // 获取公司城市
            String interceptionStr = companyCity.substring(0, companyCity.indexOf("市"));
            sql.setLength(0);
            params.clear();
            /** 本地外地查询 **/
            sql.append("SELECT COUNT( IF ( owner_address REGEXP ? OR owner_city REGEXP ?, 1, NULL )) localNumber ");
            params.add(interceptionStr);
            params.add(interceptionStr);
            sql.append(", COUNT( IF ( owner_address NOT REGEXP ? OR owner_city NOT REGEXP ?, 1, NULL )) nonLocalNumber ");
            params.add(interceptionStr);
            params.add(interceptionStr);
            sql.append(", COUNT(DISTINCT(vin)) sumNumber, brand, o_area, o_name, company_id FROM ( SELECT a.vin, a.company_id, b.o_car_brand brand ");
            sql.append(", a.active, a.prev_maintain_date, a.owner_address, a.owner_city, b.o_area, b.o_name ");
            sql.append("FROM t_crm_car a LEFT JOIN t_bc_organ b ON a.company_id = b.id WHERE a.company_id = ? ");
            params.add(map.get("companyId"));
            if (!StringUtils.isEmpty(map.get("start"))) {
                sql.append("AND a.prev_maintain_date >=? ");
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                Date date = simpleDateFormat.parse((String) map.get("start"));
                long ts = date.getTime();
                params.add(ts);
            }
            if (!StringUtils.isEmpty(map.get("stop"))) {
                sql.append("AND a.prev_maintain_date <=? ");
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                Date date = simpleDateFormat.parse((String) map.get("stop"));
                long ts = date.getTime();
                params.add(ts);
            }
            /*if(!StringUtils.isEmpty(map.get("brand"))){
                sql.append("AND a.brand = ? ");
                params.add(map.get("brand"));
            }*/
            if (!StringUtils.isEmpty(map.get("active"))) {
                sql.append("AND a.active = ? ");
                params.add(map.get("active"));
            }

            sql.append("AND a.vin IN ( SELECT DISTINCT (vin) FROM t_crm_car_workorder WHERE company_id = ? AND real_amount > '0.00' ");
            params.add(map.get("companyId"));
            sql.append("AND balance_date >= unix_timestamp( DATE_SUB(NOW(), INTERVAL 1 YEAR)) * 1000 ) GROUP BY a.vin ) a ");

            return itCrmCarDao.page(sql.toString(), params.toArray(), start, limit);
        }
        return null;


    }

    /**
     * 基盘车辆区域分布 【详情】
     *
     * @param uid
     * @param start
     * @param limit
     * @param companyId
     * @return
     * @throws Exception
     */
    @Override
    public Map<String, Object> baseCarDetaill(String uid, String start, String limit, String companyId, Map data) throws Exception {
        StringBuffer sql = new StringBuffer();
        List params = new ArrayList();


        if (!StringUtils.isEmpty(companyId)) {
            /**车市查询**/
            sql.append("SELECT id companyId,o_city,o_name,a.city FROM  t_bc_organ b LEFT JOIN ( SELECT cityid,city FROM t_bc_cities) a ON o_city = a.cityid WHERE b.id = ? ");
            params.add(companyId);
            Map companyMap = itCrmCarDao.info(sql.toString(), params.toArray());
            String companyCity = (String) companyMap.get("city"); // 获取公司城市
            String interceptionStr = companyCity.substring(0, companyCity.indexOf("市"));
            sql.setLength(0);
            params.clear();
            /**详情查询**/
            sql.append("SELECT * FROM (SELECT a.vin, a.company_id, a.brand, a.active, a.prev_maintain_date, a.owner_address, a.owner_city, b.o_area ");
            sql.append(", b.o_name, a.plate_num, a.model, a.car_age, a.series, a.owner_name, a.buy_date, ");
            sql.append("CASE WHEN a.owner_address NOT REGEXP ? OR a.owner_city NOT REGEXP ? THEN '否' ");
            params.add(interceptionStr);
            params.add(interceptionStr);
            sql.append("WHEN a.owner_address REGEXP ? OR a.owner_city REGEXP ? THEN '是' ELSE '未知' END AS fromCompany ");
            params.add(interceptionStr);
            params.add(interceptionStr);
            sql.append("FROM t_crm_car a LEFT JOIN t_bc_organ b ON a.company_id = b.id WHERE a.company_id = ? ");
            params.add(companyId);
            /*if(!StringUtils.isEmpty(data.get("brand"))){
                sql.append("AND a.brand = ? ");
                params.add(data.get("brand"));
            }*/
            if (!StringUtils.isEmpty(data.get("active"))) {
                sql.append("AND a.active = ? ");
                params.add(data.get("active"));
            }
            if (!StringUtils.isEmpty(data.get("start"))) {
                sql.append("AND a.prev_maintain_date >=? ");
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                Date date = simpleDateFormat.parse((String) data.get("start"));
                long ts = date.getTime();
                params.add(ts);
            }
            if (!StringUtils.isEmpty(data.get("stop"))) {
                sql.append("AND a.prev_maintain_date <=? ");
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                Date date = simpleDateFormat.parse((String) data.get("stop"));
                long ts = date.getTime();
                params.add(ts);
            }

            sql.append("AND a.vin IN ( SELECT DISTINCT (vin) FROM t_crm_car_workorder WHERE company_id = ? ");
            params.add(companyId);
            sql.append("AND real_amount > '0.00' AND balance_date >= unix_timestamp( DATE_SUB(NOW(), INTERVAL 1 YEAR)) * 1000 ) GROUP BY a.vin ) temp");
            return itCrmCarDao.page(sql.toString(), params.toArray(), start, limit);
        }
        return null;


    }

    /**
     * 查询车辆信息
     *
     * @param plateNum  车牌号
     * @param vin       车架号
     * @param companyId
     * @return
     * @throws Exception
     */
    @Override
    public Map<String, Object> findById(String plateNum, String vin, String companyId) throws Exception {
        if(!"".equals(vin)) {
            return itCrmCarDao.info("select id as carId,plate_num as plateNum," +
                    "owner_name as carownername,owner_phone, owner_tel as carowner_tel, owner_gender ," +
                    "contact_name as carcontact_name,contact_tel as car_contact_tel,  " +
                    "manitain_sender ,sender_tel,vin,series,model,configure,buy_date," +
                    "store_name,store_p_area,store_area,store_area_code,store_p_area_code,store_code " +
                    "from " + tabName + " where vin=? and company_id=? and vin=? limit 1 ", vin, companyId, vin);
        }else{
            return itCrmCarDao.info("select id as carId,plate_num as plateNum," +
                    "owner_name as carownername,owner_phone, owner_tel as carowner_tel, owner_gender ," +
                    "contact_name as carcontact_name,contact_tel as car_contact_tel,  " +
                    "manitain_sender ,sender_tel,vin,series,model,configure,buy_date," +
                    "store_name,store_p_area,store_area,store_area_code,store_p_area_code,store_code " +
                    "from " + tabName + " where plate_num=? and company_id=? and plate_num=? limit 1 ", plateNum, companyId, plateNum);
        }

    }


    /**
     * 查询客户标签
     */
    @Override

    public List<Map> carTable() throws Exception {
        String sql = "select * from t_bc_newdict  where status = 1 and p_code = ? ";
        List<Map<String, Object>> mapList = this.itCrmCarDao.list(sql, "02030106");
        List<Map> resList = new ArrayList<Map>();
        Map temp;
        for (Map map : mapList) {
            temp = new HashMap();
            temp.put("label", map.get("label"));
            temp.put("value", map.get("code"));
            temp.put("ischosen", false);
            resList.add(temp);
        }
        return resList;
    }

    /**
     * 根据条件查询所有车辆信息
     *
     * @param uid
     * @param key
     * @param value
     * @return
     */
    @Override
    public Map appCar(String uid, String key, String value, String start, String limit) throws Exception {
        String oid = TokenUtil.getCompanyId(uid);
        StringBuffer sqlBuffer = new StringBuffer();
        List params = new ArrayList();
        sqlBuffer.append(" select c.*,detail.u_id,bcu.u_name from t_crm_car c " +
                " left join " +
                " t_crm_car_conf_detail detail " +
                " on " +
                " c.vin=detail.vin " +
                " left JOIN " +
                " t_bc_user bcu " +
                " ON " +
                " detail.u_id=bcu.id " +
                " where c.company_id=? ");
        params.add(oid);
//        Constant.VIN_TO_SQL(sqlBuffer, uid, "c");
        confService.crmCarConfToSql(uid, sqlBuffer, "c", oid);
//        // 查询其分配的车辆
//        Map info = refCar(uid);
//        //未分配车辆
//        if (null == info || info.size() == 0) {
//            throw new Exception("该用户未分配车辆，请联系管理员！");
//        }
//
//        //车辆分配类型
//        String refType = String.valueOf(info.get("type"));
//        if (StringUtils.isEmpty(refType) || "null".equals(refType.toLowerCase())) {
//            throw new Exception("该用户未分配车辆，请联系管理员！");
//        }
//        //车辆分配值，车属号 或车尾数，vin时 另行处理
//        String refValue = String.valueOf(info.get("value"));
//        refValue = refValue.replaceAll(",", "");
//        //根据类型取车辆
//
//        switch (refType) {
//            case Constant.CAR_WH:
//                sqlBuffer.append(" AND  c.plate_num REGEXP ? ");
//                params.add("[" + refValue + "]$");
//                break;
//            case Constant.CAR_WS:
//                //先不写
//                sqlBuffer.append(" AND c.plate_num REGEXP ? ");
//                params.add(".*[" + refValue + "][A-Za-z]*$");
//                //根据尾数查询
//                break;
//            case Constant.CAR_ALL:
//                //全部车辆
//                break;
//            default:
//                throw new Exception("车辆分配错误，请联系管理员！");
//        }

        //只能查询登陆者自己所分配的车辆
        if (!value.isEmpty() && !key.isEmpty()) {
            switch (key) {
                //车牌号
                case "0":
                    sqlBuffer.append(" and c.plate_num like ? ");
                    params.add("%" + value + "%");

                    break;
                //姓名
                case "1": {
                    sqlBuffer.append(" and (c.owner_name like ? or c.manitain_sender like ? )");
                    params.add("%" + value + "%");
                    params.add("%" + value + "%");

                }
                break;
                //电话
                case "2": {
                    sqlBuffer.append(" and (c.owner_tel like ? or c.sender_tel like ?) ");
                    params.add("%" + value + "%");
                    params.add("%" + value + "%");
                }
                break;
                //电话
                case "3": {
                    sqlBuffer.append(" and c.vin like ?  ");
                    params.add("%" + value + "%");
                }
                break;
            }
        } else {
            throw new Exception("请选择查询类型并填写查询内容！");
        }

        //sqlBuffer.append(" LIMIT 1 ");
        //System.out.print(sqlBuffer.toString());
        return this.itCrmCarDao.page(sqlBuffer.toString(), params.toArray(), start, limit);

    }

    @Override
    public List carConcat(String carId) throws Exception {
        String sql = "select * from t_crm_car_contact  where car_id = ? order by create_time desc limit 20";
        return this.itCrmCarDao.list(sql, carId);
    }


    public List sPhone(String vin, String uid) throws Exception {
        List params = new ArrayList();
        String sql = "select distinct sender,sender_tel from t_crm_car_workorder where vin=? and company_id=? order by balance_date desc";
        params.add(vin);
        String cid = TokenUtil.getCompanyId(uid);
        params.add(cid);
        List<Map<String, Object>> mapList = this.itCrmCarDao.list(sql, params.toArray());
        List<Map> resList = new ArrayList<Map>();
        Map temp;
        for (Map map : mapList) {
            temp = new HashMap();
            temp.put("sender", map.get("sender"));
            temp.put("senderTel", map.get("senderTel"));
            resList.add(temp);
        }
        return resList;
    }


    @Override
    public void push(String clientId, String title, String text) throws Exception {
        if ("".equals(clientId)) {
            throw new Exception("用户未登录，推送失败！！");
        }
        IGtPush push = new IGtPush(Constant.HOST, Constant.APPKEY, Constant.MASTERSECRET);
        NotificationTemplate template = notificationTemplate(title, text);
        SingleMessage message = new SingleMessage();
        message.setOffline(true);
        // 离线有效时间，单位为毫秒，可选
        message.setOfflineExpireTime(24 * 3600 * 1000);
        message.setData(template);
        // 可选，1为wifi，0为不限制网络环境。根据手机处于的网络情况，决定是否下发
        message.setPushNetWorkType(0);
        Target target = new Target();
        target.setAppId(Constant.APPID);
        target.setClientId(clientId);
        //target.setAlias(Alias);
        IPushResult ret = null;
        try {
            ret = push.pushMessageToSingle(message, target);
        } catch (RequestException e) {
            e.printStackTrace();
            ret = push.pushMessageToSingle(message, target, e.getRequestId());
        }
        if (ret != null) {
            System.out.println(ret.getResponse().toString());
        } else {
            System.out.println("服务器响应异常");
        }
    }

    /**
     * 打开应用模板
     *
     * @param title
     * @param text
     * @return
     */
    public static NotificationTemplate notificationTemplate(String title, String text) {
        NotificationTemplate template = new NotificationTemplate();
        // 设置APPID与APPKEY
        template.setAppId(Constant.APPID);
        template.setAppkey(Constant.APPKEY);
        // 透传消息设置，1为强制启动应用，客户端接收到消息后就会立即启动应用；2为等待应用启动
        template.setTransmissionType(1);
        template.setTransmissionContent(title);
        // 设置定时展示时间
        // template.setDuration("2015-01-16 11:40:00", "2015-01-16 12:24:00");

        Style0 style = new Style0();
        // 设置通知栏标题与内容
        style.setTitle(title);
        style.setText(text);
        // 配置通知栏图标
        style.setLogo("icon.png");
        // 配置通知栏网络图标
        style.setLogoUrl("");
        // 设置通知是否响铃，震动，或者可清除
        style.setRing(true);
        style.setVibrate(true);
        style.setClearable(true);
        template.setStyle(style);

        return template;
    }

    /**
     * 打开网页模板
     *
     * @param title
     * @param text
     * @return
     */
    public static LinkTemplate linkTemplate(String title, String text) {
        LinkTemplate template = new LinkTemplate();
        // 设置APPID与APPKEY
        template.setAppId(Constant.APPID);
        template.setAppkey(Constant.APPKEY);

        Style0 style = new Style0();
        // 设置通知栏标题与内容
        style.setTitle(title);
        style.setText(text);
        // 配置通知栏图标
        style.setLogo("icon.png");
        // 配置通知栏网络图标
        style.setLogoUrl("");
        // 设置通知是否响铃，震动，或者可清除
        style.setRing(true);
        style.setVibrate(true);
        style.setClearable(true);
        template.setStyle(style);
        // 设置打开的网址地址

        template.setUrl("http://www.baidu.com");

        return template;
    }


    /**
     * 任务获得车辆详情信息
     *
     * @param vin
     * @param companyId
     */
    public Map<String, Object> getTaskCarInfo(String vin, String companyId, String taskType) throws Exception {
        taskType = taskType.substring(0, 2);
        String sql = "";
        if ("sb".equals(taskType)) {
            sql = " select car.id carId,car.vin,car.buy_date,car.owner_name ,car.owner_tel," +
                    " car.plate_num,car.series,car.model,car.configure,DATE_FORMAT(NOW(),'%Y-%m-%d') nowDate," +
                    " car.buy_date,DATE_FORMAT(FROM_UNIXTIME(car.buy_date/1000),'%Y-%m-%d') buy_time " +
                    " from  " +
                    " t_crm_car car " +
                    " where  car.vin=? and car.vin=? and car.company_id=? group by car.vin";
            return this.itCrmCarDao.info(sql, vin, vin, companyId);
        } else {
            sql = " select car.id carId,car.vin,car.owner_name ,car.owner_tel,MAX(worder.run_mile*1) run_mile,MAX(balance_date) balance_date,DATE_FORMAT(FROM_UNIXTIME(MAX(balance_date)/1000),'%Y-%m-%d')balance_time, " +
                    " car.buy_date,DATE_FORMAT(FROM_UNIXTIME(car.buy_date/1000),'%Y-%m-%d') buy_time, " +
                    " car.plate_num,car.series,car.model,car.configure,DATE_FORMAT(NOW(),'%Y-%m-%d') nowDate " +
                    " from  " +
                    " t_crm_car car left join  " +
                    " t_crm_car_workorder worder " +
                    " ON " +
                    " car.vin=worder.vin " +
                    " where  car.vin=? and car.vin=? and car.company_id=? and worder.wx_type IN ('首保','二保','定保') group by car.vin";
            Map map= this.itCrmCarDao.info(sql, vin, vin, companyId);
            if(map.size()==0){
                sql = " select car.id carId,car.vin,car.owner_name ,car.owner_tel,MAX(worder.run_mile*1) run_mile,MAX(balance_date) balance_date,DATE_FORMAT(FROM_UNIXTIME(MAX(balance_date)/1000),'%Y-%m-%d')balance_time, " +
                        " car.buy_date,DATE_FORMAT(FROM_UNIXTIME(car.buy_date/1000),'%Y-%m-%d') buy_time, " +
                        " car.plate_num,car.series,car.model,car.configure,DATE_FORMAT(NOW(),'%Y-%m-%d') nowDate " +
                        " from  " +
                        " t_crm_car car left join  " +
                        " t_crm_car_workorder worder " +
                        " ON " +
                        " car.vin=worder.vin " +
                        " where  car.vin=? and car.vin=? and car.company_id=? group by car.vin";
                return this.itCrmCarDao.info(sql, vin, vin, companyId);
            }
            return map;
        }
    }

    /**
     * 根据ID 获得详细信息
     *
     * @param carId
     * @return
     * @throws Exception
     */
    public TCrmCar findInfoByCarId(String carId) throws Exception {
        return this.itCrmCarDao.info(carId);
    }

    /**
     * 新增车主档案信息
     *
     * @param crmCar
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public void addCrmCar(Map crmCar, String uid) throws Exception {
        String companyId = TokenUtil.getCompanyId(uid);
        if ("".equals(StringUtil.formatEmpty(crmCar.get("vin")))) {
            throw new Exception("请至少提供车辆VIN信息!!");
        }
        TCrmCar crmcarInfo = this.itCrmCarDao.getCarInfo(StringUtil.formatEmpty(crmCar.get("vin")), companyId);
        //判断车主档案 是否存在
        boolean flag = false;
        if (crmcarInfo == null) {
            crmcarInfo = new TCrmCar();
            crmcarInfo.setId(StringUtil.formatEmpty(crmCar.get("id")));
            flag = true;
        } else {
            flag = false;
        }
        //将新录入的信息填写进原有的CRM中
        crmcarInfo.setOwnerName(StringUtil.formatEmpty(crmCar.get("insured")));
        crmcarInfo.setOwnerTel(StringUtil.formatEmpty(crmCar.get("contactPhone")));
        crmcarInfo.setOwnerPhone(StringUtil.formatEmpty(crmCar.get("contactPhone")));
        crmcarInfo.setAddress(StringUtil.formatEmpty(crmCar.get("insuredAddress")));
        crmcarInfo.setVin(StringUtil.formatEmpty(crmCar.get("vin")));
        crmcarInfo.setPlateNum(StringUtil.formatEmpty(crmCar.get("plateNum")));
        crmcarInfo.setEmail(StringUtil.formatEmpty(crmCar.get("email")));
        //购车日期
        if (!"".equals(StringUtil.formatEmpty(crmCar.get("firstRecordDate")))) {
            String buyDate = StringUtil.formatEmpty(crmCar.get("firstRecordDate"));
            crmcarInfo.setBuyDate(DateUtils.parseTime(buyDate, "yyyy-MM-dd"));
        }
        if (!"".equals(StringUtil.formatEmpty(crmCar.get("insuredIdNumber")))) {
            crmcarInfo.setOwnerIdentity(StringUtil.formatEmpty(crmCar.get("insuredIdNumber")));
            //根据车主VIN 公司ID 查看是否有车主档案信息
            String idNumber = StringUtil.formatEmpty(crmCar.get("insuredIdNumber"));
            IdcardInfoExtractor idcardInfo = new IdcardInfoExtractor(idNumber);
            //根据车主身份证号设置生日信息
            if (idcardInfo.getBirthday() != null) {
                crmcarInfo.setBirthday(idcardInfo.getBirthday().getTime());
            }
            //根据车主身份证号设置性别
            crmcarInfo.setOwnerGender(idcardInfo.getGender());
            crmcarInfo.setProvince(idcardInfo.getProvince());
        }
        crmcarInfo.setBrand(StringUtil.formatEmpty(crmCar.get("carBrand")));
        crmcarInfo.setSeries(StringUtil.formatEmpty(crmCar.get("carSeries")));
        crmcarInfo.setModel(StringUtil.formatEmpty(crmCar.get("carModel")));
        //sql添加
        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<>();
        if (flag) {
            //新增
            this.insCrmCar(sql, crmcarInfo, params, companyId);
        } else {
            this.updCrmCar(sql, crmcarInfo, params);
        }
        this.itCrmCarDao.executeSQL(sql.toString(), params.toArray());
    }

    @Override
    public com.hongdee.crm.car.entity.TCrmCar getInfoByVin(String vin, String companyId) throws Exception {
        return this.itCrmCarDao.getCarInfo(vin,companyId);
    }

    /**
     * 新增
     *
     * @param sql
     * @param crmcarInfo
     * @param params
     */
    private void insCrmCar(StringBuffer sql, TCrmCar crmcarInfo, List<Object> params, String companyId) {
        sql.append("insert into t_crm_car(id,owner_name,owner_tel,owner_phone,owner_address,vin,plate_num,buy_date," +
                "owner_identity,owner_birthday,owner_gender,owner_province,brand,series,model,company_id,owner_email) " +
                "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        params.add(crmcarInfo.getId());
        params.add(crmcarInfo.getOwnerName());
        params.add(crmcarInfo.getOwnerTel());
        params.add(crmcarInfo.getOwnerPhone());
        params.add(crmcarInfo.getAddress());
        params.add(crmcarInfo.getVin());
        params.add(crmcarInfo.getPlateNum());
        params.add(crmcarInfo.getBuyDate());
        params.add(crmcarInfo.getOwnerIdentity());
        params.add(crmcarInfo.getBirthday());
        params.add(crmcarInfo.getOwnerGender());
        params.add(crmcarInfo.getProvince());
        params.add(crmcarInfo.getBrand());
        params.add(crmcarInfo.getSeries());
        params.add(crmcarInfo.getModel());
        params.add(companyId);
        params.add(crmcarInfo.getEmail());
    }

    /**
     * 编辑
     *
     * @param sql
     * @param crmcarInfo
     * @param params
     */
    private void updCrmCar(StringBuffer sql, TCrmCar crmcarInfo, List<Object> params) {
        //修改
        sql.append("update t_crm_car set owner_name=?,owner_tel=?,owner_phone=?,owner_address=?,vin=?,plate_num=?,buy_date=?," +
                " owner_identity=?,owner_birthday=?,owner_gender=?,owner_province=?,brand=?,series=?,model=?,owner_email=? where id=? ");
        params.add(crmcarInfo.getOwnerName());
        params.add(crmcarInfo.getOwnerTel());
        params.add(crmcarInfo.getOwnerPhone());
        params.add(crmcarInfo.getAddress());
        params.add(crmcarInfo.getVin());
        params.add(crmcarInfo.getPlateNum());
        params.add(crmcarInfo.getBuyDate());
        params.add(crmcarInfo.getOwnerIdentity());
        params.add(crmcarInfo.getBirthday());
        params.add(crmcarInfo.getOwnerGender());
        params.add(crmcarInfo.getProvince());
        params.add(crmcarInfo.getBrand());
        params.add(crmcarInfo.getSeries());
        params.add(crmcarInfo.getModel());
        params.add(crmcarInfo.getEmail());
        params.add(crmcarInfo.getId());

    }


}
